package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.FeedbackDto;
import com.hualude.roadsign.dto.HomepageAboutDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.Query;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;

/**
 * Created by xu-jiaHui on 2017/4/28.
 */
public class FeedbackRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<FeedbackDto> getFeedBack4Dt(DTRequestDto dtRequestDto) throws Exception {
        String sql = "SELECT f.*,u.userName as rapporteurName from tb_feedback f,tb_user u where f.RapporteurId=u.userId and " +
                "CONCAT(u.UserName,f.FeedBackContent,f.FeedBackCreateTime) like '%" + dtRequestDto.getSearch() + "%' " +
                " order by f.FeedBackCreateTime desc ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(FeedbackDto.class));
        List<FeedbackDto> feedbackDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return feedbackDtos;
    }

    public Integer getFeedBackCount4Dt(String search) throws Exception {
        String sql = "SELECT count(*) from tb_feedback f,tb_user u where f.RapporteurId=u.userId and " +
                "CONCAT(u.UserName,f.FeedBackContent,f.FeedBackCreateTime) like '%" + search + "%' " +
                " order by f.FeedBackCreateTime desc ";
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<HomepageAboutDto> getAboutInfo(Integer infoId) throws Exception {
        String sql = "select InfoId, OrgTele, OrgFax, OrgMail, OrgWebsite " +
                "from tb_homepage_about where InfoId = " + infoId;
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HomepageAboutDto.class));
        return query.getResultList();
    }

}
